package com.jb.repair.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

import com.jb.repair.F1Application;
import com.jb.repair.acticity.CheckLocActivity;
import com.jb.repair.common.AppConfig;
import com.jb.repair.model.FileLbModel;
import com.jb.repair.model.QxjlModel;
import com.jb.repair.model.RegularWorkModel;
import com.jb.repair.model.ResultModel;
import com.jb.repair.model.ResultStatisticModel;
import com.jb.repair.model.TaskTableModel;
import com.jb.repair.model.YcjlModel;
import com.jb.repair.util.MessageUtil;
import com.jb.repair.util.TimeUtil;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by gaobin on 2015/11/24.
 */
public class LocalDbDao {

    private DBHelper dbHelper;
    private SQLiteDatabase db;
    private static LocalDbDao dao;
    private String TAG = "LocalDbDao";
    private Context context;

    private LocalDbDao(Context context) {
        dbHelper = new DBHelper(context);
        this.context = context;
    }

    public synchronized static LocalDbDao getInstance(Context context) {
        if (dao == null)
            dao = new LocalDbDao(context);
        return dao;
    }

    /**
     * 计算统计数据
     * @param taskIds {String} 待统计的任务的id
     */
    public List<ResultStatisticModel>  calStatistic(String userId, List<String> taskIds) {
        // 拉取下来的任务库
        SQLiteDatabase hub = context.getApplicationContext().openOrCreateDatabase(AppConfig.DB_PATh, Context.MODE_PRIVATE, null);
        // 本地库
//        SQLiteDatabase local = dbHelper.getReadableDatabase();

        List<ResultStatisticModel> list = new ArrayList<ResultStatisticModel>();

        if (taskIds == null || taskIds.size() == 0) {
            return list;
        }

        // 当前时间
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date curDate = new Date(System.currentTimeMillis());//获取当前时间
        String current = formatter.format(curDate);

        for (String id : taskIds) {
            // 实检项目数量
            String sql1 = "select count(*) from tb_si_task_result where si_time IS NOT NULL and task_id = '" + id + "' and si_time != ''";
            // 任务项目总量
            String sql2 = "select count(*) from tb_si_task_result where task_id = '" + id + "'";
            // 必检实检项目完成数量
            String sql3 = "select count(*) from tb_si_task_result where si_time IS NOT NULL and task_id = '" + id + "' and is_must_today = 'T' and si_time != ''";
            // 必检项目总量
            String sql4 = "select count(*) from tb_si_task_result where task_id = '" + id + "' and is_must_today = 'T'";
            // 异常数量
            String sql5 = "select count(*) from tb_si_task_result where is_abnormal = 'T' and task_id = '" + id + "'";
            // 缺陷数量
            String sql6 = "select count(*) from tb_si_task_result where is_DEFECT = 'T' and task_id = '" + id + "'";
            // 点检时间
            String sql7 = "select distinct(si_time) from tb_si_task_result where task_id = '" + id + "'";

            ResultStatisticModel model = new ResultStatisticModel();
            model.task_id = id;
            model.upload_time = current;
            model.si_person = userId;

            Cursor c;

            // 实检项目数量
//            c = local.rawQuery(sql1, null);
            c = hub.rawQuery(sql1, null);
            model.total_si_num = c.moveToFirst() ? c.getInt(0) : 0;
            Log.i(TAG,"实检项目总数量:"+model.total_si_num);
            c.close();

            // 任务项目总数量
            c = hub.rawQuery(sql2, null);
            int temp = c.moveToFirst()?c.getInt(0):0;
            Log.i(TAG,"任务项目总数量:"+ temp);
            // 漏检量
            model.total_forgot_num = temp - model.total_si_num;
            Log.i(TAG,"总漏检数量:"+model.total_forgot_num);
            c.close();

            // 必检实检项目完成数量
 //           c = local.rawQuery(sql3, null);
            c = hub.rawQuery(sql3, null);
            model.total_must_si_com_num = c.moveToFirst() ? c.getInt(0) : 0;
            Log.i(TAG,"必检项目完成数量:"+model.total_must_si_com_num);
            c.close();

            // 必检项目总量
            c = hub.rawQuery(sql4, null);
            model.total_must_si_num = c.moveToFirst() ? c.getInt(0) : 0;
            Log.i(TAG,"必检项目总数量:"+model.total_must_si_com_num);
            c.close();

            // 必检项目漏检数量
            model.total_must_si_forgot_num = model.total_must_si_num - model.total_must_si_com_num;
            Log.i(TAG,"必检项目漏检数量:"+model.total_must_si_forgot_num);

            // 异常数量
 //           c = local.rawQuery(sql5, null);
            c = hub.rawQuery(sql5, null);
            model.YC_COUNT = c.moveToFirst() ? c.getInt(0) : 0;
            Log.i(TAG,"必检项目漏检数量:"+model.total_must_si_forgot_num);
            c.close();

            // 缺陷数量
 //           c = local.rawQuery(sql6, null);
            c = hub.rawQuery(sql6, null);
            model.QX_COUNT = c.moveToFirst() ? c.getInt(0) : 0;
            c.close();

            // 点检时间
 //           c = local.rawQuery(sql7, null);
            c = hub.rawQuery(sql7, null);
            model.si_time = c.moveToFirst() ? c.getString(0) : "";
            c.close();

            list.add(model);

        }
        hub.close();

        return list;
    }

    public Boolean insertResult(ResultModel model) {
        db = dbHelper.getWritableDatabase();
        Long result;
        Cursor c = db.rawQuery("select * from " + DBHelper.SI_RESULT_TABLE_NAME + " where GUID='" + model.GUID + "'", null);
        if (c.moveToNext()) {  //表中存在该条记录执行改操作
            ContentValues cv = new ContentValues();
            cv.put("desc_result", model.desc_result);
            cv.put("num_result", model.num_result);
            cv.put("is_abnormal", model.is_abnormal);
            cv.put("is_defect", model.is_defect);
            cv.put("defect_desc", model.defect_desc);
            cv.put("defect_level_id", model.defect_level_id);
            cv.put("defect_type_guid", model.defect_type_id);
            cv.put("si_person_id", F1Application.getUserId());
            cv.put("si_person", F1Application.getUserName());
            cv.put("si_time", TimeUtil.getSystemTime());
            cv.put("task_id", CheckLocActivity.task_id);
            cv.put("abnormal_desc", model.abnormal_desc);
            cv.put("is_must_today", model.is_must_today);
            result = new Long(db.update(DBHelper.SI_RESULT_TABLE_NAME, cv, "GUID=?", new String[]{model.GUID}));
        } else { //表中没有该条记录执行插入操作
            ContentValues cv = new ContentValues();
            cv.put("GUID", model.GUID);
            cv.put("desc_result", model.desc_result);
            cv.put("num_result", model.num_result);
            cv.put("is_abnormal", model.is_abnormal);
            cv.put("is_defect", model.is_defect);
            cv.put("defect_desc", model.defect_desc);
            cv.put("defect_level_id", model.defect_level_id);
            cv.put("defect_type_guid", model.defect_level_id);
            cv.put("si_person_id", model.si_person_id);
            cv.put("si_person", model.si_person);
            cv.put("si_time", model.si_time);
            cv.put("abnormal_desc", model.abnormal_desc);
            cv.put("defect_type_guid", model.defect_type_id);
            cv.put("si_person_id", F1Application.getUserId());
            cv.put("si_person", F1Application.getUserName());
            cv.put("si_time", TimeUtil.getSystemTime());
            cv.put("task_id", CheckLocActivity.task_id);
            cv.put("is_must_today", model.is_must_today);

            result = db.insert(DBHelper.SI_RESULT_TABLE_NAME, null, cv);

            c.close();
            db.close();
        }
        return result == -1 ? false : true;
    }

    /**
     * 获取所有点检结果
     * @param repeat
     * @return
     */
    public List<ResultModel> getAllResult(Boolean repeat) {
        db = dbHelper.getWritableDatabase();
        List<ResultModel> result = new ArrayList<>();
        String sql;
        if (repeat)
            sql = "select * from " + DBHelper.SI_RESULT_TABLE_NAME;
        else
            sql = "select * from " + DBHelper.SI_RESULT_TABLE_NAME + " where is_repeat='T'";
        Log.i(TAG, sql);
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            ResultModel model = new ResultModel();
            model.GUID = c.getString(c.getColumnIndex("GUID"));
            model.defect_type_id = c.getString(c.getColumnIndex("defect_type_guid"));
            model.defect_level_id = c.getString(c.getColumnIndex("defect_level_id"));
            model.abnormal_desc = c.getString(c.getColumnIndex("abnormal_desc"));
            model.defect_describe = c.getString(c.getColumnIndex("defect_describe"));
            model.desc_result = c.getString(c.getColumnIndex("desc_result"));
            model.num_result = c.getString(c.getColumnIndex("num_result"));
            model.is_defect = c.getString(c.getColumnIndex("is_defect"));
            model.is_abnormal = c.getString(c.getColumnIndex("is_abnormal"));
            model.si_person = c.getString(c.getColumnIndex("si_person"));
            model.si_person_id = c.getString(c.getColumnIndex("si_person_id"));
            model.si_time = c.getString(c.getColumnIndex("si_time"));
            model.task_id = c.getString(c.getColumnIndex("task_id"));
            model.power_plant_id = F1Application.getPowerPlantId();
            result.add(model);
        }
        c.close();
        db.close();
        return result;
    }

    public List<YcjlModel> getAllAbnormal() {
        db = dbHelper.getWritableDatabase();
        List<YcjlModel> result = new ArrayList<>();
        String sql = "select * from " + DBHelper.ABNORMAL_TABLE_NAME;
        Log.i(TAG, sql);
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            YcjlModel model = new YcjlModel();
            model.creat_time = c.getString(c.getColumnIndex("creat_time"));
            model.guid = c.getString(c.getColumnIndex("guid"));
            model.abn_describe = c.getString(c.getColumnIndex("abn_describe"));
            model.foundman_id = c.getString(c.getColumnIndex("foundman_id"));
            model.creat_user_id = c.getString(c.getColumnIndex("creat_user_id"));
            model.eq_id = c.getString(c.getColumnIndex("eq_id"));
            model.power_plant_id = F1Application.getPowerPlantId();
            model.source_id = c.getString(c.getColumnIndex("source_id"));
            model.found_time = c.getString(c.getColumnIndex("found_time"));
            model.inspect_id = c.getString(c.getColumnIndex("inspect_id"));
            model.dealtype_id = "0303301";
            model.solved_time_editable = model.creat_time;
            result.add(model);
        }
        c.close();
        db.close();
        Log.i("main", "异常个数" + result.size());
        return result;
    }

    public List<QxjlModel> getAllDefect() {
        db = dbHelper.getWritableDatabase();
        List<QxjlModel> result = new ArrayList<>();
        String sql = "select * from " + DBHelper.DEFECT_TABLE_NAME;
        Log.i(TAG, sql);
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            QxjlModel model = new QxjlModel();
            model.guid = c.getString(c.getColumnIndex("guid"));
            model.defect_describe = c.getString(c.getColumnIndex("defect_describe"));
            model.creat_time = c.getString(c.getColumnIndex("creat_time"));
            model.creat_user_id = c.getString(c.getColumnIndex("creat_user_id"));
            model.eq_id = c.getString(c.getColumnIndex("eq_id"));
            model.level_id = c.getString(c.getColumnIndex("level_id"));
            model.type_id = c.getString(c.getColumnIndex("type_id"));
            model.power_plant_id = F1Application.getPowerPlantId();
            model.source_id = c.getString(c.getColumnIndex("source_id"));
            model.found_time = c.getString(c.getColumnIndex("found_time"));
            model.foundman_id = c.getString(c.getColumnIndex("foundman_id"));
            model.inspect_id = c.getString(c.getColumnIndex("inspect_id"));
            model.solved_time_editable = model.creat_time;
            result.add(model);
        }
        c.close();
        db.close();
        return result;
    }

    public Boolean delectALLResult() {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DBHelper.SI_RESULT_TABLE_NAME, null, null);
        db.close();
        return result == -1 ? false : true;
    }

    public Boolean insertAbnormal(ContentValues cv) {
        db = dbHelper.getWritableDatabase();
        Long result = db.insert(DBHelper.ABNORMAL_TABLE_NAME, null, cv);
        db.close();
        Log.i("main", "插入异常结果：" + result);
        return result == -1 ? false : true;
    }

    public Boolean delectALLAbnormal() {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DBHelper.ABNORMAL_TABLE_NAME, null, null);
        db.close();
        return result == -1 ? false : true;
    }

    public Boolean insertDefect(ContentValues cv) {
        db = dbHelper.getWritableDatabase();
        Long result = db.insert(DBHelper.DEFECT_TABLE_NAME, null, cv);
        db.close();
        return result == -1 ? false : true;
    }

    public Boolean delectALLDefect() {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DBHelper.DEFECT_TABLE_NAME, null, null);
        db.close();
        return result == -1 ? false : true;
    }

    public Boolean insertFile(FileLbModel model) {
        db = dbHelper.getWritableDatabase();
        Long result;
        ContentValues cv = new ContentValues();
        cv.put("GUID", model.guid);
        cv.put("WJMC", model.wjmc);
        cv.put("LX", model.lx);
        cv.put("TIMEPOINT",model.TIMEPOINT);
        cv.put("table_name",model.table);
        cv.put("line_id",model.lineId);
        cv.put("column_name",model.clounm);
        result = db.insert(DBHelper.FILE_TABLE_NAME, null, cv);
        db.close();
        return result == -1 ? false : true;
    }

    public List<FileLbModel> getAllFile() {
        db = dbHelper.getWritableDatabase();
        List<FileLbModel> result = new ArrayList<>();
        String sql = "select * from " + DBHelper.FILE_TABLE_NAME;
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            FileLbModel model = new FileLbModel();
            model.obj_id = c.getString(c.getColumnIndex("OBJ_ID"));
            model.guid = c.getString(c.getColumnIndex("GUID"));
            model.wjmc = c.getString(c.getColumnIndex("WJMC"));
            model.lineId = c.getString(c.getColumnIndex("line_id"));
            model.table = c.getString(c.getColumnIndex("table_name"));
            model.clounm = c.getString(c.getColumnIndex("column_name"));
            result.add(model);
        }
        c.close();
        db.close();
        Log.i("main", "文件个数：" + result.size());
        return result;
    }

    public boolean delectAllFile() {
        db = dbHelper.getWritableDatabase();
        int result = db.delete(DBHelper.FILE_TABLE_NAME, null, null);
        db.close();
        return result == -1 ? false : true;
    }

    public Boolean delectDb() {
        return dbHelper.deleteDatabase(context);
    }

    public Boolean hasData() {
        db = dbHelper.getWritableDatabase();
        Cursor c = db.rawQuery("select * from " + DBHelper.SI_RESULT_TABLE_NAME, null);
        if (c.moveToNext())
            return true;
        Cursor c1 = db.rawQuery("select * from " + DBHelper.ABNORMAL_TABLE_NAME, null);
        if (c1.moveToNext())
            return true;
        Cursor c2 = db.rawQuery("select * from " + DBHelper.DEFECT_TABLE_NAME, null);
        if (c2.moveToNext())
            return true;
        Cursor c3 = db.rawQuery("select * from " + DBHelper.FILE_TABLE_NAME, null);
        if (c3.moveToNext())
            return true;
        return false;
    }

    public int getRepeatResultnum() {
        int result = -1;
        db = dbHelper.getWritableDatabase();
        String sql = "select count(*) from " + DBHelper.SI_RESULT_TABLE_NAME + " where is_repeat='T'";
        Cursor c = db.rawQuery(sql, null);
        try {
            result = c.getInt(0);
        }catch (Exception e){
            return 0;
        }
        c.close();
        return result;
    }

    public void modifyResult(String guid,ContentValues cv){
        db = dbHelper.getWritableDatabase();
        db.update(DBHelper.SI_RESULT_TABLE_NAME,cv,"GUID=?",new String[]{guid});
        db.close();
    }

    public void delectByResultId(String resultId){
        db = dbHelper.getWritableDatabase();
        String sql = "select attachment from TB_EQ_ABNORMAL where inspect_id='"+ resultId +"'";
        Cursor c = db.rawQuery(sql,null);
        while (c.moveToNext()){
            String attachment = c.getString(0);
            db.delete(DBHelper.FILE_TABLE_NAME,"GUID=?",new String[]{attachment});
//            File file =new File(AppConfig.UPLOAD_PATH + attachment + ".jpg");
//            if(file.exists()){
//                file.delete();
//            }
        }

        String sql1 = "select attachment from TB_EQ_DEFECT where inspect_id='"+ resultId +"'";
        Cursor c1 = db.rawQuery(sql1,null);
        while (c1.moveToNext()){
            String attachment = c1.getString(0);
            db.delete(DBHelper.FILE_TABLE_NAME,"GUID=?",new String[]{attachment});
  //          File file =new File(AppConfig.UPLOAD_PATH + attachment + ".jpg");
//            if(file.exists()){
//                file.delete();
//                Log.i("gaobin", "缺陷文件删除");
//            }
        }
        c.close();
        c1.close();

        db.delete("TB_EQ_ABNORMAL", "inspect_id=?", new String[]{resultId});
        db.delete("TB_EQ_DEFECT", "inspect_id=?", new String[]{resultId});
        db.close();
    }

    public void delectResultById(String id){
        db = dbHelper.getWritableDatabase();
        db.delete(DBHelper.SI_RESULT_TABLE_NAME,"GUID=?",new String[]{id});
        db.close();
    }

    /**
     * 将定期工作插入到数据库 TB_CT_TASK 表中
     *
     * @param regularWorkModelList 定期工作列表
     */
    public void insertIntoCtTask(List<RegularWorkModel> regularWorkModelList) {

        if (regularWorkModelList != null) {
            db = dbHelper.getWritableDatabase();

            int len = regularWorkModelList.size();
            Log.i(TAG, "定期工作列表长度为:" + len);
            String sql = "select * from " + DBHelper.CT_TASK_TABLE_NAME + " where guid like '?'";
            Long result = null;
            RegularWorkModel regularWorkModel = null;
            for (int i = 0; i < len; i++) {
                //检查是否已存在
                regularWorkModel = regularWorkModelList.get(i);
                Log.i(TAG, "regularWorkModel = " + regularWorkModel.toString());
                sql = sql.replace("?", regularWorkModel.GUID);
                Log.i(TAG, "insertIntoCtTask sql 语句为：" + sql);
                Cursor c = db.rawQuery(sql, null);
                sql = "select * from " + DBHelper.CT_TASK_TABLE_NAME + " where guid like '?'";
                if (!c.moveToNext()) {
                    //表示数据库中没有该条记录，插入数据库
                    ContentValues cv = new ContentValues();
                    cv.put("guid", regularWorkModel.GUID);
                    cv.put("plan_id", regularWorkModel.PLAN_ID);
                    cv.put("task_name", regularWorkModel.TASK_NAME);
                    cv.put("gznr", regularWorkModel.GZNR);
                    cv.put("task_status", regularWorkModel.TASK_STATUS);
                    cv.put("push_status", regularWorkModel.PUSH_STATUS);
                    cv.put("ct_person_id", regularWorkModel.CT_PERSON_ID);
                    cv.put("should_finish_time", regularWorkModel.SHOULD_FINISH_TIME);
                    cv.put("remind_time", regularWorkModel.REMIND_TIME);
                    cv.put("finished_time", regularWorkModel.SHOULD_FINISH_TIME);
                    cv.put("registered_time", regularWorkModel.REGISTERED_TIME);
                    cv.put("delay_time", regularWorkModel.DELAY_TIME == null || regularWorkModel.DELAY_TIME.equals("null") ? regularWorkModel.SHOULD_FINISH_TIME : regularWorkModel.DELAY_TIME);
                    cv.put("delay_result", regularWorkModel.DELAY_RESULT);
                    cv.put("cancel_result", regularWorkModel.cancel_result);
                    cv.put("completion", regularWorkModel.completion);
                    cv.put("attachment", regularWorkModel.attachment);
                    cv.put("attachment_path", regularWorkModel.attachment_path);
                    cv.put("note", regularWorkModel.note);
                    cv.put("is_enable", regularWorkModel.is_enable);
                    cv.put("creat_person_id", regularWorkModel.CREAT_PERSON_ID);
                    cv.put("power_plant_id", regularWorkModel.POWER_PLANT_ID);
                    cv.put("creat_time", regularWorkModel.CREAT_TIME);
                    cv.put("update_time", regularWorkModel.update_time);
                    result = db.insert(DBHelper.CT_TASK_TABLE_NAME, null, cv);
                    Log.i(TAG, "insert result = " + result);
                } else {
                    Log.i(TAG, "数据已存在，不再重复插入");
                }

            }
            db.close();
        }

    }


    /**
     * 将任务表插入到数据库 TB_CT_TASK_MODEL 表中
     *
     * @param taskTableModelList 定期工作任务表（模板）列表
     */
    public void insertIntoCtTaskModel(List<TaskTableModel> taskTableModelList) {

        if (taskTableModelList != null) {
            db = dbHelper.getWritableDatabase();

            int len = taskTableModelList.size();
            Log.i(TAG, "任务列表长度为:" + len);
            String sql = "select * from " + DBHelper.CT_TASK_MODEL_NAME + " where guid like '?'";
            Long result = null;
            TaskTableModel taskTableModel = null;
            for (int i = 0; i < len; i++) {
                //检查是否已存在
                taskTableModel = taskTableModelList.get(i);
                Log.i(TAG, "taskTableModel = " + taskTableModel.toString());
                sql = sql.replace("?", taskTableModel.GUID);
                Log.i(TAG, "insertIntoCtTaskModel sql 语句为：" + sql);
                Cursor c = db.rawQuery(sql, null);
                sql = "select * from " + DBHelper.CT_TASK_MODEL_NAME + " where guid like '?'";
                if (!c.moveToNext()) {
                    //表示数据库中没有该条记录，插入数据库
                    ContentValues cv = new ContentValues();
                    cv.put("GUID", taskTableModel.GUID);
                    cv.put("EQ_ID", taskTableModel.EQ_ID);
                    cv.put("CREATE_TIME", taskTableModel.CREATE_TIME);
                    cv.put("ATTACHEQ_ID", taskTableModel.ATTACHEQ_ID);
                    cv.put("PLAN_ID", taskTableModel.PLAN_ID);
                    cv.put("IS_ENABLE", taskTableModel.IS_ENABLE);
                    cv.put("MBID", taskTableModel.MBID);
                    cv.put("TASK_CONTENT", taskTableModel.TASK_CONTENT);
                    cv.put("CREATE_USER_ID", taskTableModel.CREATE_USER_ID);
                    cv.put("NOTE", taskTableModel.NOTE);
                    cv.put("UPDATE_TIME", taskTableModel.UPDATE_TIME);
                    cv.put("TASK_ID", taskTableModel.TASK_ID);
                    cv.put("SFYX", taskTableModel.SFYX);
                    cv.put("MBFJID", taskTableModel.MBFJID);
                    cv.put("ZYMC", taskTableModel.ZYMC);
                    cv.put("ISTJ", taskTableModel.ISTJ);
                    cv.put("MBMC", taskTableModel.MBMC);
                    cv.put("POWER_PLANT_ID", taskTableModel.POWER_PLANT_ID);
                    cv.put("LAYER_CODE", taskTableModel.LAYER_CODE);
                    cv.put("FJID", taskTableModel.FJID);
                    cv.put("EQ_NAME", taskTableModel.EQ_NAME);
                    cv.put("JZMC", taskTableModel.JZMC);
                    cv.put("STATUS", "0");//0表示未下载 1-未上传 2- 已上传
                    cv.put("UPLOAD_TIME", "2000-01-01 11:11");
                    result = db.insert(DBHelper.CT_TASK_MODEL_NAME, null, cv);
                    Log.i(TAG, "insert result = " + result);
                } else {
                    Log.i(TAG, "数据已存在，不再重复插入");
                }

            }
            db.close();
        }

    }

    /**
     * 获取最新的 num 条任务
     * @param userId 用户ID
     * @param offset
     */
    public List<RegularWorkModel> getLastRegularWorks(String userId, int limit, int offset) {
        List<RegularWorkModel> list = new ArrayList<RegularWorkModel>();
        db = dbHelper.getWritableDatabase();
//        //删除原表
//        db.execSQL("DROP TABLE TB_CT_TASK");
        String sql = "select * from " + DBHelper.CT_TASK_TABLE_NAME +
                " where ct_person_id = '" + userId + "'" +
                " and (task_status ='0700207' or task_status ='0700205' or task_status ='0700203' or task_status ='0700201')" + //只显示"未完成"、"超期未完成"、"已延期"、"未开始"的任务
                " order by 'delay_time' desc";
//                " limit " + limit + " offset " + offset;
        Log.i(TAG, "getLastRegularWorks sql 语句为：" + sql);
        Cursor c = null;
        try {
            c = db.rawQuery(sql, null);
        } catch (SQLiteException e) {
            //数据表不存在，创建数据表
            Log.i(TAG, "数据表不存在，重新创建");
            // 定期工作表
            db.execSQL("CREATE TABLE TB_CT_TASK (" +
                    "GUID          VARCHAR( 42 )      PRIMARY KEY," +
                    "plan_id       VARCHAR( 42 )," +
                    "task_name     VARCHAR( 20 )," +
                    "gznr   VARCHAR( 256 )," +
                    "task_status   VARCHAR( 20 )," +
                    "push_status   VARCHAR( 20 )," +
                    "ct_person_id  VARCHAR( 42 )," +
                    "ct_person_name  VARCHAR( 42 )," +
                    "should_finish_time       DATETIME," +
                    "remind_time        DATETIME," +
                    "finished_time    DATETIME," +
                    "registered_time  DATETIME," +
                    "delay_time       DATETIME," +
                    "delay_result     TEXT," +
                    "cancel_result  TEXT," +
                    "completion     TEXT," +
                    "attachment     VARCHAR( 256 )," +
                    "attachment_path     VARCHAR( 256 )," +
                    "note     VARCHAR( 512 )," +
                    "is_enable     VARCHAR( 42 )," +
                    "creat_person_id     VARCHAR( 42 )," +
                    "power_plant_id     VARCHAR( 42 )," +
                    "creat_time     DATETIME," +
                    "update_time    DATETIME" +
                    ")"
            );
            c = db.rawQuery(sql, null);
        }

        Log.i(TAG, "getLastRegularWorks 记录数为：" + c.getCount() + "");
        try {
            while (c.moveToNext()) {
                RegularWorkModel regularWorkModel = new RegularWorkModel(
                        c.getString(c.getColumnIndex("GUID")),
                        c.getString(c.getColumnIndex("plan_id")),
                        c.getString(c.getColumnIndex("task_name")),
                        c.getString(c.getColumnIndex("gznr")),
                        c.getString(c.getColumnIndex("task_status")),
                        c.getString(c.getColumnIndex("ct_person_id")),
                        c.getString(c.getColumnIndex("ct_person_name")),
                        c.getString(c.getColumnIndex("should_finish_time")),
                        c.getString(c.getColumnIndex("remind_time")),
                        c.getString(c.getColumnIndex("finished_time")),
                        c.getString(c.getColumnIndex("registered_time")),
                        c.getString(c.getColumnIndex("delay_time")),
                        c.getString(c.getColumnIndex("delay_result")),
                        c.getString(c.getColumnIndex("cancel_result")),
                        c.getString(c.getColumnIndex("completion")),
                        c.getString(c.getColumnIndex("attachment")),
                        c.getString(c.getColumnIndex("attachment_path")),
                        c.getString(c.getColumnIndex("note")),
                        c.getInt(c.getColumnIndex("is_enable")),
                        c.getString(c.getColumnIndex("creat_person_id")),
                        c.getString(c.getColumnIndex("power_plant_id")),
                        c.getString(c.getColumnIndex("creat_time")),
                        c.getString(c.getColumnIndex("update_time"))
                );
                list.add(regularWorkModel);
//                Log.i(TAG, "delay_time = " + regularWorkModel.DELAY_TIME);
//                Log.i(TAG, "delay_result = " + regularWorkModel.DELAY_RESULT);
            }
        } catch (IllegalStateException e) {
            e.printStackTrace();
        }

        db.close();

        return list;
    }

    /**
     * 获取搜索结果
     *
     * @param userId 用户ID
     * @param num    数目
     * @param params 搜索参数
     */
    public List<RegularWorkModel> getSearchResult(String userId, int num, Map params) {
        List<RegularWorkModel> list = new ArrayList<RegularWorkModel>();
        db = dbHelper.getWritableDatabase();
        String statusStr = params.get("status").toString().trim().equals("0700200") ? " task_status!='0700206' and" : " task_status = '" + params.get("status").toString().trim() + "' and";
        String sql = "select * from " + DBHelper.CT_TASK_TABLE_NAME +
                " where ct_person_id = '" + userId + "' and" +
                statusStr +
                " creat_time>='" + params.get("creat_start_date") + " 00:00' and creat_time<='" + params.get("creat_end_date") + " 23:59' and" +
                " delay_time>='" + params.get("finish_start_date") + " 00:00' and delay_time<='" + params.get("finish_end_date") + " 23:59'";
//                " limit " + num;
        Log.i(TAG, "getSearchResult sql 语句为：" + sql);
        Cursor c = null;
        try {
            c = db.rawQuery(sql, null);
        } catch (SQLiteException e) {
            //数据表不存在，创建数据表
            Log.i(TAG, "数据表不存在，重新创建");
            // 定期工作表
            db.execSQL("CREATE TABLE TB_CT_TASK (" +
                    "GUID          VARCHAR( 42 )      PRIMARY KEY," +
                    "plan_id       VARCHAR( 42 )," +
                    "task_name     VARCHAR( 20 )," +
                    "gznr   VARCHAR( 256 )," +
                    "task_status   VARCHAR( 20 )," +
                    "push_status   VARCHAR( 20 )," +
                    "ct_person_id  VARCHAR( 42 )," +
                    "ct_person_name  VARCHAR( 42 )," +
                    "should_finish_time       DATETIME," +
                    "remind_time        DATETIME," +
                    "finished_time    DATETIME," +
                    "registered_time  DATETIME," +
                    "delay_time       DATETIME," +
                    "delay_result     TEXT," +
                    "cancel_result  TEXT," +
                    "completion     TEXT," +
                    "attachment     VARCHAR( 256 )," +
                    "attachment_path     VARCHAR( 256 )," +
                    "note     VARCHAR( 512 )," +
                    "is_enable     VARCHAR( 42 )," +
                    "creat_person_id     VARCHAR( 42 )," +
                    "power_plant_id     VARCHAR( 42 )," +
                    "creat_time     DATETIME," +
                    "update_time    DATETIME" +
                    ")"
            );
            c = db.rawQuery(sql, null);
        }

        Log.i(TAG, "getSearchResult 记录数为：" + c.getCount() + "");
        try {
            while (c.moveToNext()) {
                RegularWorkModel regularWorkModel = new RegularWorkModel(
                        c.getString(c.getColumnIndex("GUID")),
                        c.getString(c.getColumnIndex("plan_id")),
                        c.getString(c.getColumnIndex("task_name")),
                        c.getString(c.getColumnIndex("gznr")),
                        c.getString(c.getColumnIndex("task_status")),
                        c.getString(c.getColumnIndex("ct_person_id")),
                        c.getString(c.getColumnIndex("ct_person_name")),
                        c.getString(c.getColumnIndex("should_finish_time")),
                        c.getString(c.getColumnIndex("remind_time")),
                        c.getString(c.getColumnIndex("finished_time")),
                        c.getString(c.getColumnIndex("registered_time")),
                        c.getString(c.getColumnIndex("delay_time")),
                        c.getString(c.getColumnIndex("delay_result")),
                        c.getString(c.getColumnIndex("cancel_result")),
                        c.getString(c.getColumnIndex("completion")),
                        c.getString(c.getColumnIndex("attachment")),
                        c.getString(c.getColumnIndex("attachment_path")),
                        c.getString(c.getColumnIndex("note")),
                        c.getInt(c.getColumnIndex("is_enable")),
                        c.getString(c.getColumnIndex("creat_person_id")),
                        c.getString(c.getColumnIndex("power_plant_id")),
                        c.getString(c.getColumnIndex("creat_time")),
                        c.getString(c.getColumnIndex("update_time"))
                );
                list.add(regularWorkModel);
            }
        } catch (IllegalStateException e) {
            e.printStackTrace();
        }

        db.close();

        return list;
    }


    /**
     * 获取定期工作对应的任务表
     * @param taskId
     * @return
     */
    public List<TaskTableModel> getTaskTableListById(String taskId) {
        List<TaskTableModel> list = new ArrayList<TaskTableModel>();

        db = dbHelper.getWritableDatabase();
//        //删除原表
//        db.execSQL("DROP TABLE TB_CT_TASK_MODEL");
        String sql = "select * from " + DBHelper.CT_TASK_MODEL_NAME + " where TASK_ID like '" + taskId + "'";
        Log.i(TAG, "getTaskTableListById sql 语句为：" + sql);
        Cursor c = null;
        try {
            c = db.rawQuery(sql, null);
        } catch (SQLiteException e) {
            //数据表不存在，创建数据表
            Log.i(TAG, "数据表不存在，重新创建");
            // 定期工作表
            db.execSQL("CREATE TABLE TB_CT_TASK_MODEL (" +
                    "GUID          VARCHAR( 42 )      PRIMARY KEY," +
                    "EQ_ID       VARCHAR( 42 )," +
                    "CREATE_TIME     DATETIME," +
                    "ATTACHEQ_ID   VARCHAR( 50 )," +
                    "PLAN_ID   VARCHAR( 42 )," +
                    "IS_ENABLE   VARCHAR( 20 )," +
                    "MBID  VARCHAR( 42 )," +
                    "TASK_CONTENT  VARCHAR( 256 )," +
                    "CREATE_USER_ID   VARCHAR( 42 )," +
                    "NOTE        VARCHAR( 512 )," +
                    "UPDATE_TIME    DATETIME," +
                    "TASK_ID       VARCHAR( 42 )," +
                    "SFYX      VARCHAR( 42 )," +
                    "MBFJID VARCHAR( 42 )," +
                    "ZYMC     VARCHAR( 50 )," +
                    "ISTJ     VARCHAR( 20 )," +
                    "MBMC     VARCHAR( 256 )," +
                    "POWER_PLANT_ID     VARCHAR( 50 )," +
                    "LAYER_CODE     VARCHAR( 42 )," +
                    "FJID     VARCHAR( 42 )," +
                    "EQ_NAME     VARCHAR( 256 )," +
                    "JZMC     VARCHAR( 42 )," +
                    "STATUS     VARCHAR( 2 )," +
                    "UPLOAD_TIME     DATETIME" +
                    ")"
            );
            c = db.rawQuery(sql, null);
        }

        Log.i(TAG, "getTaskTableListById 记录数为：" + c.getCount() + "");
        try {
            while (c.moveToNext()) {
                TaskTableModel taskTableModel = new TaskTableModel(
                        c.getString(c.getColumnIndex("GUID")),
                        c.getString(c.getColumnIndex("EQ_ID")),
                        c.getString(c.getColumnIndex("CREATE_TIME")),
                        c.getString(c.getColumnIndex("ATTACHEQ_ID")),
                        c.getString(c.getColumnIndex("PLAN_ID")),
                        c.getString(c.getColumnIndex("IS_ENABLE")),
                        c.getString(c.getColumnIndex("MBID")),
                        c.getString(c.getColumnIndex("TASK_CONTENT")),
                        c.getString(c.getColumnIndex("CREATE_USER_ID")),
                        c.getString(c.getColumnIndex("NOTE")),
                        c.getString(c.getColumnIndex("UPDATE_TIME")),
                        c.getString(c.getColumnIndex("TASK_ID")),
                        c.getString(c.getColumnIndex("SFYX")),
                        c.getString(c.getColumnIndex("MBFJID")),
                        c.getString(c.getColumnIndex("ZYMC")),
                        c.getString(c.getColumnIndex("ISTJ")),
                        c.getString(c.getColumnIndex("MBMC")),
                        c.getString(c.getColumnIndex("POWER_PLANT_ID")),
                        c.getString(c.getColumnIndex("LAYER_CODE")),
                        c.getString(c.getColumnIndex("FJID")),
                        c.getString(c.getColumnIndex("EQ_NAME")),
                        c.getString(c.getColumnIndex("JZMC")),
                        c.getString(c.getColumnIndex("STATUS")),
                        c.getString(c.getColumnIndex("UPLOAD_TIME"))
                );
                list.add(taskTableModel);
            }
        } catch (IllegalStateException e) {
            e.printStackTrace();
        }

        db.close();

        return list;
    }


    /**
     * 更新定期工作的内容
     *
     * @param regularWorkModel
     */
    public void updateRegularWork(int type, RegularWorkModel regularWorkModel) {
        db = dbHelper.getWritableDatabase();
        //首先判断该定期工作存不存在
        String querySql = "select * from " + DBHelper.CT_TASK_TABLE_NAME + " where guid = '?'";
        querySql = querySql.replace("?", regularWorkModel.GUID);
        Log.i(TAG, "querySql = " + querySql);
        try {
            Cursor c = db.rawQuery(querySql, null);
            if (c.moveToNext()) {
                //存在
                //更新记录
                ContentValues cv = new ContentValues();
                if (type == 0) {
                    //未完成
                    cv.put("update_time", regularWorkModel.update_time);
                } else if (type == 1) {
                    //延期
                    cv.put("delay_time", regularWorkModel.DELAY_TIME);
                    cv.put("delay_result", regularWorkModel.DELAY_RESULT);
                    cv.put("update_time", regularWorkModel.update_time);
                } else if (type == 2) {
                    //取消
                    cv.put("cancel_result", regularWorkModel.cancel_result);
                    cv.put("update_time", regularWorkModel.update_time);
                } else if (type == 3) {
                    //完工
                    cv.put("finished_time", regularWorkModel.FINISHED_TIME);
                    cv.put("update_time", regularWorkModel.update_time);
                }
                Log.i(TAG, "task_status = " + regularWorkModel.TASK_STATUS);
                cv.put("task_status", regularWorkModel.TASK_STATUS);

                db.update(DBHelper.CT_TASK_TABLE_NAME, cv, "guid=?", new String[]{regularWorkModel.GUID});
                MessageUtil.showToast(context, "保存成功");

                c.close();
            } else {
                Log.i(TAG, "不存在该定期工作");
                MessageUtil.showToast(context, "保存失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            db.close();
            MessageUtil.showToast(context, "保存失败");
        }
        db.close();
    }

    /**
     * 更新表中的字段
     *
     * @param tableName
     * @param columnName
     * @param value
     */
    public void update(String tableName, String columnName, String value, String guid) {
        db = dbHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(columnName, value);
        try {
            db.update(tableName, cv, "guid=?", new String[]{guid});
            db.close();
        } catch (Exception e) {
            e.printStackTrace();
            db.close();
        }


    }

}